/*
This code produces summary statistics for PPP loans
*/


use "${datadir}/sba/public_all", clear

gen app_year = substr(dateapproved, 7,4)


//Commented code not currently used for anything
/*
gen bucket = 1 if initialapprovalamount < 4e+4
replace bucket = 2 if initialapprovalamount >= 4e+4 & initialapprovalamount < 1.5e+5
replace bucket = 3 if initialapprovalamount >= 1.5e+5 & initialapprovalamount < 2e+6
replace bucket = 4 if initialapprovalamount >= 2e+6

tab bucket app_year
tab bucket processingmethod

tab bucket app_year [aw=initialapprovalamount]

gen bucket2 = 1 if currentapprovalamount < 4e+4
replace bucket2 = 2 if currentapprovalamount >= 4e+4 & currentapprovalamount < 1.5e+5
replace bucket2 = 3 if currentapprovalamount >= 1.5e+5 & currentapprovalamount < 2e+6
replace bucket2 = 4 if currentapprovalamount >= 2e+6

tab bucket2 app_year
tab bucket2 processingmethod

replace currentapprovalamount = round(currentapprovalamount)
tab bucket2 app_year [fw=currentapprovalamount]

gen loan_amt = currentapprovalamount - undisbursedamount

gen bucket3 = 1 if loan_amt < 4e+4
replace bucket3 = 2 if loan_amt >= 4e+4 & loan_amt < 1.5e+5
replace bucket3 = 3 if loan_amt >= 1.5e+5 & loan_amt < 2e+6
replace bucket3 = 4 if loan_amt >= 2e+6

tab bucket3 app_year
tab bucket3 processingmethod

tab bucket3 app_year [aw=loan_amt]

gen bucket4 = 1 if forgivenessamount < 4e+4
replace bucket4 = 2 if forgivenessamount >= 4e+4 & forgivenessamount < 1.5e+5
replace bucket4 = 3 if forgivenessamount >= 1.5e+5 & forgivenessamount < 2e+6
replace bucket4 = 4 if forgivenessamount >= 2e+6

tab bucket4 app_year
tab bucket4 processingmethod

tab bucket4 app_year [aw=forgivenessamount]
*/

//Now use the firm size bins

gen firm_size = 1 if jobsreported >= 1 & jobsreported <= 9
replace firm_size = 2 if jobsreported > 9 & jobsreported <= 49
replace firm_size = 3 if jobsreported > 49 & jobsreported <= 99
replace firm_size = 4 if jobsreported > 99 & jobsreported <= 249
replace firm_size = 5 if jobsreported > 249



//First versus second round loans
gen round = processingmethod == "PPS"

preserve
gcollapse (sum) forgivenessamount initialapprovalamount (count) loannumber , by(round firm_size)

export excel "${outdir}/sum_stats/sba_distribution2.xlsx", replace firstrow(variables)
restore
